<?php
namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithTitle;

use Illuminate\Support\Facades\DB;
use App\Models\CxkProduct;
use App\Models\CxkWarehouseProduct;
use App\Models\CxkCheckData;
use App\Models\CxkCard;
use Illuminate\Support\Facades\Auth;


// use App\Export\BaseExport;
/*
 * 导出产品及库存
 */
class ProductExport  implements FromCollection,WithHeadings,WithEvents,WithColumnFormatting,WithTitle
{

    private $cid;
    private $whid;
    
    public function __construct($cid, $whid) {
        $this->cid = $cid;
        $this->whid = $whid;
    }
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        
        $userid = Auth::user()->id;
        // $card = CxkProduct::with(["warehouseproduct" => function ($query) {
        //             $query->where('whid', $this->whid)->with("warehouse");
        //         }])->where([['userid','=', $userid],['cid', '=', $this->cid]])->get();
                
        $card = CxkCard::with(["whproduct" => function ($query){
                    $query->where('whid', $this->whid)->with("warehouse");
                    },'product'])->where([['userid','=', $userid],['cid', '=', $this->cid],['whid', '=', $this->whid]])->get();
        // dd($card->toArray()[0]);    
        //获得监盘数据和第三方审核数据
        // $checkdata = CxkCheckData::where([['cid', '=', $this->cid],['whid', '=', $this->whid]])->whereRaw("substirng(ctime,1,10) = '".date("Y-m-d")."'")->groupBy('productid')->get();
        $sub_query = CxkCheckData::where([['cid', '=', $this->cid],['whid', '=', $this->whid]])->orderBy('id','desc');
        $checkdata = CxkCheckData::select('*')
                ->from(DB::raw('('.$sub_query->toSql().') as a'))
                ->mergeBindings($sub_query->getQuery())
                ->groupBy('productid', 'role_key')->get();
                // dd($checkdata->toArray());
        //过滤监盘数据和第三方审核数据
        $jianpan_kc = [];
        $sanfang_kc = [];
        foreach ($checkdata as $k => $v){
            if ($v->role_key == 'ID_JIANPAN'){
                $jianpan_kc[$v->productid] = $v->toArray();
            }else if ($v->role_key == 'ID_SANFANG'){
                $sanfang_kc[$v->productid] = $v->toArray();
            }
        }
        // dd($jianpan_kc, $sanfang_kc);
        $tmpdata = [];     
        $aid = [];   
        

        $excel_data = [];
        $n = 0;
        foreach ($card as $k => $row) {
            $productid = $row->whproduct->productid??0;
            $excel_data[] = [
                $n+1,
                $row->product_code,
                $row->pinming,
                $row->unit,
                isset($row->whproduct->warehouse->name)?$row->whproduct->warehouse->name:"",
                isset($row->whproduct->stock_num)?$row->whproduct->stock_num:0,
                '',
                isset($jianpan_kc[$productid]['snum'])?$jianpan_kc[$productid]['snum']:0,
                isset($jianpan_kc[$productid]['sname'])?$jianpan_kc[$productid]['sname']:"",
                isset($jianpan_kc[$productid]['ctime'])?$jianpan_kc[$productid]['ctime']:"",
                isset($sanfang_kc[$productid]['snum'])?$sanfang_kc[$productid]['snum']:0,
                isset($sanfang_kc[$productid]['sname'])?$sanfang_kc[$productid]['sname']:"",
                isset($sanfang_kc[$productid]['ctime'])?$sanfang_kc[$productid]['ctime']:"",
                
            ];
            $n++;
        }
        // dd($excel_data);
        //$footer = ['执行:', $filename, '', "合计:", "已支付 {$pay_num} 人", "支付总金额 {$amount} 元", "未支付 {$not_pay} 人"];
        $excel_data[] = [''];
        // $excel_data[] = [''];
        // $excel_data[] = [''];

           
        return  collect($excel_data); 
    }

    /**
     * 设置excel第一行头信息
     * @return array
     */
    public function headings(): array
    {
        return ['序号', '产品编号', '产品名称', '单位', '所属仓库', '库存', '负责人', '监盘数', '监盘人', '监盘日期', '三方审核数', '审核人', '审核日期'];
    }

    /**
     * 设置sheet的名称
     * @return string
     */
    public function title(): string
    {
        return '产品库存盘点表';
    }

    /**
     * @return array
     */
    public function columnFormats(): array
    {
        return [
            'B' => NumberFormat::FORMAT_TEXT,//K列为字符串
            'C' => NumberFormat::FORMAT_TEXT,//L列为字符串
            'D' => NumberFormat::FORMAT_TEXT,//D列为字符串
            'E' => NumberFormat::FORMAT_TEXT,//E列为字符串
            'G' => NumberFormat::FORMAT_TEXT,//G列为字符串
            'I' => NumberFormat::FORMAT_TEXT,//I列为字符串
            'J' => NumberFormat::FORMAT_TEXT,//J列为字符串
            'L' => NumberFormat::FORMAT_TEXT,//L列为字符串
            'M' => NumberFormat::FORMAT_TEXT,//M列为字符串
        ];
    }

    /**
     * @return array
     */
    public function registerEvents(): array
    {
        return [
            AfterSheet::class    => function(AfterSheet $event) {
                $cellRange = 'A1:M1'; // All headers
                $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(12);

                $event->sheet->getDelegate()->getColumnDimension('A')->setWidth(6);    
                $event->sheet->getDelegate()->getColumnDimension('B')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('C')->setWidth(60);
                $event->sheet->getDelegate()->getColumnDimension('D')->setWidth(11);
                $event->sheet->getDelegate()->getColumnDimension('E')->setWidth(9);
                $event->sheet->getDelegate()->getColumnDimension('F')->setWidth(8);
                $event->sheet->getDelegate()->getColumnDimension('G')->setWidth(8);
                $event->sheet->getDelegate()->getColumnDimension('H')->setWidth(10);
                $event->sheet->getDelegate()->getColumnDimension('I')->setWidth(10);
                $event->sheet->getDelegate()->getColumnDimension('J')->setWidth(15);
                $event->sheet->getDelegate()->getColumnDimension('K')->setWidth(10);
                $event->sheet->getDelegate()->getColumnDimension('L')->setWidth(10);
                $event->sheet->getDelegate()->getColumnDimension('M')->setWidth(15);

                // 总行数(包含标题头)
                $heights = $event->sheet->getDelegate()->getHighestRow();
                // 总列数(字母 A B 等)
                $rows = $event->sheet->getDelegate()->getHighestColumn();

                // //区域单元格 - 字体、颜色、背景、对齐等
                $event->sheet->getDelegate()->getStyle('A1:M' . $heights)->applyFromArray([
                    // vertical 水平对齐 - horizontal 垂直居中
                    'alignment' => [
                        'vertical'   => Alignment::VERTICAL_CENTER,
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                    ],
                    'borders' => [
                            'allBorders' => [
                                'borderStyle' => 'hair',
                                'color' => ['argb' => '000000'],
                            ],
                        ]
                ]);


                // 设置行高，$i为数据行数
                for ($i = 1; $i <= $heights; $i++) {
                    $event->sheet->getDelegate()->getRowDimension($i)->setRowHeight(20);
                    // $event->sheet->getDelegate()->getRowDimension($i)->setBold(true);
                }

                $event->sheet->getDelegate()->getStyle('A1:M1')->applyFromArray([
                    'font' => [
                        'name' => 'Arial',
                        'bold' => true,
                        'italic' => false,
                        'strikethrough' => false,
                        'color' => [
                            'rgb' => 'FFFFFF'
                        ]
                    ],
                    'fill' => [
                        'fillType' => 'linear', //线性填充，类似渐变
                        'rotation' => 45, //渐变角度
                        'startColor' => [
                            'rgb' => '54AE54' //初始颜色
                        ],
                        //结束颜色，如果需要单一背景色，请和初始颜色保持一致
                        'endColor' => [
                            'argb' => '54AE54'
                        ]
                    ]
                ]);

                

            },
        ];
    }

}
